using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
/*
 * 
 * Autor      : Jose Jornando de Carvalho Junior    
 * Data       : 10/04/2010 07:07:41
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace SERIX
{
   public class COpcoesQuestoes
   {
       private string IDT_TIQ ;
       private string IDT_CAT ;
       private string IDT_QUE ;
       private string SEQ_OPCAO ;
       private string IDT_DESC ;
       private string DSC_OPCAO ;

	   /// <sumary>
       /// Construtor da classe opcoes_questoes
       /// <sumary>
       public COpcoesQuestoes()
       {
   
       }

	   /// <sumary>
       /// Construtor da classe Opcoes_questoes
       /// <sumary>
       public COpcoesQuestoes( string IDT_TIQ, string IDT_CAT, string IDT_QUE, string SEQ_OPCAO, string IDT_DESC, string DSC_OPCAO )
       {
          this.IDT_TIQ = IDT_TIQ;
          this.IDT_CAT = IDT_CAT;
          this.IDT_QUE = IDT_QUE;
          this.SEQ_OPCAO = SEQ_OPCAO;
          this.IDT_DESC = IDT_DESC;
          this.DSC_OPCAO = DSC_OPCAO;
       }
	   
	   #region Propriedades
     	/// <sumary>
        /// Identificador do tipo de questionario
        /// <sumary>
        public string Idt_tiq 
		{
		   get
		   {
		       return this.IDT_TIQ;
		   }
		   set
		   {
		       this.IDT_TIQ = value;
		   }
		}
     	/// <sumary>
        /// Identificador da categoria do questionario
        /// <sumary>
        public string Idt_cat 
		{
		   get
		   {
		       return this.IDT_CAT;
		   }
		   set
		   {
		       this.IDT_CAT = value;
		   }
		}
     	/// <sumary>
        /// Identifcador da questao
        /// <sumary>
        public string Idt_que 
		{
		   get
		   {
		       return this.IDT_QUE;
		   }
		   set
		   {
		       this.IDT_QUE = value;
		   }
		}
     	/// <sumary>
        /// Sequencial da opcao da questao
        /// <sumary>
        public string Seq_opcao 
		{
		   get
		   {
		       return this.SEQ_OPCAO;
		   }
		   set
		   {
		       this.SEQ_OPCAO = value;
		   }
		}
     	/// <sumary>
        /// Identificador da descricao da questao
        /// <sumary>
        public string Idt_desc 
		{
		   get
		   {
		       return this.IDT_DESC;
		   }
		   set
		   {
		       this.IDT_DESC = value;
		   }
		}
     	/// <sumary>
        /// Descricao da opcao da questao
        /// <sumary>
        public string Dsc_opcao 
		{
		   get
		   {
		       return this.DSC_OPCAO;
		   }
		   set
		   {
		       this.DSC_OPCAO = value;
		   }
		}
	    #endregion Propriedades
	
	    /// <summary>
        /// Inserindo um novo Opcoes_questoes.
        /// </summary>
        public void Inserir(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_OPCOES_QUESTOES
			                  ( IDT_TIQ, IDT_CAT, IDT_QUE, SEQ_OPCAO, IDT_DESC, DSC_OPCAO )
						   values
						      ( :IDT_TIQ, :IDT_CAT, :IDT_QUE, :SEQ_OPCAO, :IDT_DESC, :DSC_OPCAO )";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("IDT_TIQ",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_CAT",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_QUE",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("SEQ_OPCAO",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_DESC",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("DSC_OPCAO",OracleType.VarChar)); 
				
                command.Parameters["IDT_TIQ"].Value = this.IDT_TIQ;
                command.Parameters["IDT_CAT"].Value = this.IDT_CAT;
                command.Parameters["IDT_QUE"].Value = this.IDT_QUE;
                command.Parameters["SEQ_OPCAO"].Value = this.SEQ_OPCAO;
                if (String.IsNullOrEmpty(this.IDT_DESC))
                {
                    command.Parameters["IDT_DESC"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["IDT_DESC"].Value = this.IDT_DESC;
                }  
                if (String.IsNullOrEmpty(this.DSC_OPCAO))
                {
                    command.Parameters["DSC_OPCAO"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["DSC_OPCAO"].Value = this.DSC_OPCAO;
                }  
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	    /// <summary>
        /// Alterando um Opcoes_questoes.
        /// </summary>
        public void Alterar(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SRX_OPCOES_QUESTOES set 
			                   IDT_DESC = :IDT_DESC, DSC_OPCAO = :DSC_OPCAO
						   where
						       IDT_TIQ = :IDT_TIQ and IDT_CAT = :IDT_CAT and IDT_QUE = :IDT_QUE and SEQ_OPCAO = :SEQ_OPCAO ";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("IDT_TIQ",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_CAT",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_QUE",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("SEQ_OPCAO",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_DESC",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("DSC_OPCAO",OracleType.VarChar)); 
				
                command.Parameters["IDT_TIQ"].Value = this.IDT_TIQ;
                command.Parameters["IDT_CAT"].Value = this.IDT_CAT;
                command.Parameters["IDT_QUE"].Value = this.IDT_QUE;
                command.Parameters["SEQ_OPCAO"].Value = this.SEQ_OPCAO;
                if (String.IsNullOrEmpty(this.IDT_DESC))
                {
                    command.Parameters["IDT_DESC"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["IDT_DESC"].Value = this.IDT_DESC;
                }  
                if (String.IsNullOrEmpty(this.DSC_OPCAO))
                {
                    command.Parameters["DSC_OPCAO"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["DSC_OPCAO"].Value = this.DSC_OPCAO;
                }  
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	     /// <summary>
        /// Exclui um  Opcoes_questoes.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SRX_OPCOES_QUESTOES", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login,BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("IDT_TIQ", DbType.Int32, this.IDT_TIQ));
            tab.adicionarFiltro(new Coluna("IDT_CAT", DbType.Int32, this.IDT_CAT));
            tab.adicionarFiltro(new Coluna("IDT_QUE", DbType.Int32, this.IDT_QUE));
            tab.adicionarFiltro(new Coluna("SEQ_OPCAO", DbType.Int32, this.SEQ_OPCAO));
            tab.delete();
        }
		
		static public DataTable ObterTodos(string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_OPCOES_QUESTOES order by DSC_OPCAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)); 
                return tbl; 
                
            }
            catch (Exception e)
            {
 
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		
		static public COpcoesQuestoes PesquisaporCodigo(string idt_tiq, string idt_cat, string idt_que, string seq_opcao, string login)
        {
            COpcoesQuestoes obj;
            string SQL = @"SELECT *
                            FROM  Serix.SRX_OPCOES_QUESTOES 
                            WHERE IDT_TIQ = :IDT_TIQ and IDT_CAT = :IDT_CAT and IDT_QUE = :IDT_QUE and SEQ_OPCAO = :SEQ_OPCAO";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.AdicionarParametro(":IDT_TIQ", DbType.String, idt_tiq);
				qy.AdicionarParametro(":IDT_CAT", DbType.String, idt_cat);
				qy.AdicionarParametro(":IDT_QUE", DbType.String, idt_que);
				qy.AdicionarParametro(":SEQ_OPCAO", DbType.String, seq_opcao);
               
				qy.Executar();
                obj = new COpcoesQuestoes( qy.ObterCampo("IDT_TIQ"), qy.ObterCampo("IDT_CAT"), qy.ObterCampo("IDT_QUE"), qy.ObterCampo("SEQ_OPCAO"), qy.ObterCampo("IDT_DESC"), qy.ObterCampo("DSC_OPCAO") );
            }                                  
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new COpcoesQuestoes();
            }
			finally
            {
                qy.FecharConexao();
            }
            return obj;
        }

        static public int ObterNovoIdt(string idt_tiq, string idt_cat, string idt_que, string login)
        {
            string SQL = @"SELECT PKG_BIBSERIX.OBTER_SEQ_OPCAO(:idt_tiq, :idt_cat, :idt_que) IDT From dual ";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
                qy.AdicionarParametro("idt_tiq", DbType.Int32, idt_tiq);
                qy.AdicionarParametro("idt_cat", DbType.Int32, idt_cat);
                qy.AdicionarParametro("idt_que", DbType.Int32, idt_que);
				qy.Executar();
				return Convert.ToInt32(qy.ObterCampo("IDT"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return-1;
            }
			finally
            {
                qy.FecharConexao();
            }
        }

        static public DataTable ObterPorIDT_TIQ(string idt_tiq, string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_OPCOES_QUESTOES  where IDT_TIQ = :IDT_TIQ order by DSC_OPCAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_TIQ", OracleType.Number));
                command.Parameters["IDT_TIQ"].Value = idt_tiq;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
        static public DataTable ObterPorIDT_CAT(string idt_cat, string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_OPCOES_QUESTOES  where IDT_CAT = :IDT_CAT order by DSC_OPCAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_CAT", OracleType.Number));
                command.Parameters["IDT_CAT"].Value = idt_cat;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
        static public DataTable ObterPorIDT_QUE(string idt_que, string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_OPCOES_QUESTOES  where IDT_QUE = :IDT_QUE order by DSC_OPCAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_QUE", OracleType.Number));
                command.Parameters["IDT_QUE"].Value = idt_que;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }

        static public DataTable ObterPorIDT_DESC( string idt_desc, string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_OPCOES_QUESTOES  where IDT_DESC = :IDT_DESC order by DSC_OPCAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_DESC", OracleType.Number));
                command.Parameters["IDT_DESC"].Value = idt_desc;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }

        static public DataTable ObterOpcoesPorQuestao(string idt_tiq, string idt_cat, string idt_que, string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select o.idt_tiq, o.idt_cat, o.idt_que, o.seq_opcao, o.idt_desc, o.dsc_opcao, d.idt_desc, d.tpo_vinc, d.descricao
                              from serix.srx_opcoes_questoes o
                              left join serix.srx_descricoes_opcoes d on (o.idt_desc = d.idt_desc)
                             where idt_tiq = :idt_tiq
                               and idt_cat = :idt_cat
                               and idt_que = :idt_que
                             order by descricao";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_TIQ", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_CAT", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_QUE", OracleType.Number));
                command.Parameters["IDT_TIQ"].Value = idt_tiq;
                command.Parameters["IDT_CAT"].Value = idt_cat;
                command.Parameters["IDT_QUE"].Value = idt_que;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }

         
    }
}	

